﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Catering_DBOpertion;
using System.Data;

namespace Catering_Operation
{
    public class BalanceInfo
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Catering_Model.BalanceInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO Balance(");
            strSql.Append("[RawID],[InDBId],[ClassID],[AddDate],[Balance],[Price],[ChangeNumber],[ChangePrice],[ChangeType],[ChangeID],[PreBalance],[PrePrice])");
            strSql.Append(" VALUES (");
            strSql.Append("@RawID,@InDBId,@ClassID,@AddDate,@Balance,@Price,@ChangeNumber,@ChangePrice,@ChangeType,@ChangeID,@PreBalance,@PrePrice)");
            SqlParameter[] parameters = {
					new SqlParameter("@RawID", SqlDbType.Int,4),
					new SqlParameter("@InDBId", SqlDbType.NVarChar,50),
					new SqlParameter("@ClassID", SqlDbType.Int,4),
					new SqlParameter("@AddDate", SqlDbType.DateTime,12),
					new SqlParameter("@Balance", SqlDbType.Decimal,18),
                    new SqlParameter("@Price", SqlDbType.Decimal,18),
					new SqlParameter("@ChangeNumber", SqlDbType.Decimal,18),
					new SqlParameter("@ChangePrice", SqlDbType.Decimal,18),
					new SqlParameter("@ChangeType", SqlDbType.Int,4),
					new SqlParameter("@ChangeID", SqlDbType.Int,4),
                    new SqlParameter("@PreBalance", SqlDbType.Decimal,18),
					new SqlParameter("@PrePrice", SqlDbType.Decimal,18),
                                        };
            parameters[0].Value = model.RawID;
            parameters[1].Value = model.InDBId;
            parameters[2].Value = model.ClassID;
            parameters[3].Value = model.AddDate;
            parameters[4].Value = model.Balance;
            parameters[5].Value = model.Price;
            parameters[6].Value = model.ChangeNumber;
            parameters[7].Value = model.ChangePrice;
            parameters[8].Value = model.ChangeType;
            parameters[9].Value = model.ChangeID;
            parameters[10].Value = model.PreBalance;
            parameters[11].Value = model.PrePrice;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(decimal Balance, decimal Price, decimal PreBalance, decimal PrePrice, int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE [Balance] SET ");
            strSql.Append("Balance=@Balance,");
            strSql.Append("Price=@Price,");
            strSql.Append("PreBalance=@PreBalance,");
            strSql.Append("PrePrice=@PrePrice");
            strSql.Append(" WHERE ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@Balance", SqlDbType.Decimal,18),
					new SqlParameter("@Price", SqlDbType.Decimal,18),
					new SqlParameter("@PreBalance", SqlDbType.Decimal,18),
					new SqlParameter("@PrePrice", SqlDbType.Decimal,18),
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = Balance;
            parameters[1].Value = Price;
            parameters[2].Value = PreBalance;
            parameters[3].Value = PrePrice;
            parameters[4].Value = ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            string strSql = "DELETE FROM [Balance] WHERE ID=@ID ";
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql, parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Catering_Model.BalanceInfo GetModel(int id)
        {

            string strSql = "SELECT TOP 1 [ID],[RawID],[InDBId],[ClassID],[AddDate],[Balance],[Price],[ChangeNumber],[ChangePrice],[ChangeType],[ChangeID],[PreBalance],[PrePrice] FROM [Balance] WHERE ID=@ID ";
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = id;

            Catering_Model.BalanceInfo model = new Catering_Model.BalanceInfo();
            DataTable dt = DbHelperSQL.Query(strSql, parameters).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                if (!(dt.Rows[0]["ID"] is DBNull) && dt.Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(dt.Rows[0]["id"].ToString());
                }
                if (!(dt.Rows[0]["RawID"] is DBNull) && dt.Rows[0]["RawID"].ToString() != "")
                {
                    model.RawID = int.Parse(dt.Rows[0]["RawID"].ToString());
                }
                if (!(dt.Rows[0]["InDBId"] is DBNull) && dt.Rows[0]["InDBId"].ToString() != "")
                {
                    model.InDBId = dt.Rows[0]["InDBId"].ToString();
                }
                if (!(dt.Rows[0]["ClassID"] is DBNull) && dt.Rows[0]["ClassID"].ToString() != "")
                {
                    model.ClassID = int.Parse(dt.Rows[0]["ClassID"].ToString());
                }
                if (!(dt.Rows[0]["AddDate"] is DBNull) && dt.Rows[0]["AddDate"].ToString() != "")
                {
                    model.AddDate = DateTime.Parse(dt.Rows[0]["AddDate"].ToString());
                }

                if (!(dt.Rows[0]["Balance"] is DBNull) && dt.Rows[0]["Balance"].ToString() != "")
                {
                    model.Balance = Decimal.Parse(dt.Rows[0]["Balance"].ToString());
                }
                if (!(dt.Rows[0]["Price"] is DBNull) && dt.Rows[0]["Price"].ToString() != "")
                {
                    model.Price = Decimal.Parse(dt.Rows[0]["Price"].ToString());
                }
                if (!(dt.Rows[0]["ChangeNumber"] is DBNull) && dt.Rows[0]["ChangeNumber"].ToString() != "")
                {
                    model.ChangeNumber = Decimal.Parse(dt.Rows[0]["ChangeNumber"].ToString());
                }
                if (!(dt.Rows[0]["ChangePrice"] is DBNull) && dt.Rows[0]["ChangePrice"].ToString() != "")
                {
                    model.ChangePrice = Decimal.Parse(dt.Rows[0]["ChangePrice"].ToString());
                }
                if (!(dt.Rows[0]["ChangeType"] is DBNull) && dt.Rows[0]["ChangeType"].ToString() != "")
                {
                    model.ChangeType = int.Parse(dt.Rows[0]["ChangeType"].ToString());
                }
                if (!(dt.Rows[0]["ChangeID"] is DBNull) && dt.Rows[0]["ChangeID"].ToString() != "")
                {
                    model.ChangeID = int.Parse(dt.Rows[0]["ChangeID"].ToString());
                }
                if (!(dt.Rows[0]["PreBalance"] is DBNull) && dt.Rows[0]["PreBalance"].ToString() != "")
                {
                    model.PreBalance = Decimal.Parse(dt.Rows[0]["PreBalance"].ToString());
                }
                if (!(dt.Rows[0]["PrePrice"] is DBNull) && dt.Rows[0]["PrePrice"].ToString() != "")
                {
                    model.PrePrice = Decimal.Parse(dt.Rows[0]["PrePrice"].ToString());
                }
                dt.Dispose();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Catering_Model.BalanceInfo GetModel(string strWhere, string orderBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TOP 1 [ID],[RawID],[InDBId],[ClassID],[AddDate],[Balance],[Price],[ChangeNumber],[ChangePrice],[ChangeType],[ChangeID],[PreBalance],[PrePrice] FROM [Balance] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" WHERE " + strWhere);
            }
            if (orderBy.Trim() != "")
            {
                strSql.Append(" ORDER BY " + orderBy);
            }

            Catering_Model.BalanceInfo model = new Catering_Model.BalanceInfo();
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                if (!(dt.Rows[0]["ID"] is DBNull) && dt.Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(dt.Rows[0]["id"].ToString());
                }
                if (!(dt.Rows[0]["RawID"] is DBNull) && dt.Rows[0]["RawID"].ToString() != "")
                {
                    model.RawID = int.Parse(dt.Rows[0]["RawID"].ToString());
                }
                if (!(dt.Rows[0]["InDBId"] is DBNull) && dt.Rows[0]["InDBId"].ToString() != "")
                {
                    model.InDBId = dt.Rows[0]["InDBId"].ToString();
                }
                if (!(dt.Rows[0]["ClassID"] is DBNull) && dt.Rows[0]["ClassID"].ToString() != "")
                {
                    model.ClassID = int.Parse(dt.Rows[0]["ClassID"].ToString());
                }
                if (!(dt.Rows[0]["AddDate"] is DBNull) && dt.Rows[0]["AddDate"].ToString() != "")
                {
                    model.AddDate = DateTime.Parse(dt.Rows[0]["AddDate"].ToString());
                }

                if (!(dt.Rows[0]["Balance"] is DBNull) && dt.Rows[0]["Balance"].ToString() != "")
                {
                    model.Balance = Decimal.Parse(dt.Rows[0]["Balance"].ToString());
                }
                if (!(dt.Rows[0]["Price"] is DBNull) && dt.Rows[0]["Price"].ToString() != "")
                {
                    model.Price = Decimal.Parse(dt.Rows[0]["Price"].ToString());
                }
                if (!(dt.Rows[0]["ChangeNumber"] is DBNull) && dt.Rows[0]["ChangeNumber"].ToString() != "")
                {
                    model.ChangeNumber = Decimal.Parse(dt.Rows[0]["ChangeNumber"].ToString());
                }
                if (!(dt.Rows[0]["ChangePrice"] is DBNull) && dt.Rows[0]["ChangePrice"].ToString() != "")
                {
                    model.ChangePrice = Decimal.Parse(dt.Rows[0]["ChangePrice"].ToString());
                }
                if (!(dt.Rows[0]["ChangeType"] is DBNull) && dt.Rows[0]["ChangeType"].ToString() != "")
                {
                    model.ChangeType = int.Parse(dt.Rows[0]["ChangeType"].ToString());
                }
                if (!(dt.Rows[0]["ChangeID"] is DBNull) && dt.Rows[0]["ChangeID"].ToString() != "")
                {
                    model.ChangeID = int.Parse(dt.Rows[0]["ChangeID"].ToString());
                }
                if (!(dt.Rows[0]["PreBalance"] is DBNull) && dt.Rows[0]["PreBalance"].ToString() != "")
                {
                    model.PreBalance = Decimal.Parse(dt.Rows[0]["PreBalance"].ToString());
                }
                if (!(dt.Rows[0]["PrePrice"] is DBNull) && dt.Rows[0]["PrePrice"].ToString() != "")
                {
                    model.PrePrice = Decimal.Parse(dt.Rows[0]["PrePrice"].ToString());
                }
                dt.Dispose();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere, string orderBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT [ID],[RawID],[InDBId],[ClassID],[AddDate],[Balance],[Price],[ChangeNumber],[ChangePrice],[ChangeType],[ChangeID],[PreBalance],[PrePrice] FROM [Balance] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" WHERE " + strWhere);
            }
            if (orderBy.Trim() != "")
            {
                strSql.Append(" ORDER BY " + orderBy);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(int top, string strWhere, string orderBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TOP " + top + " [ID],[RawID],[InDBId],[ClassID],[AddDate],[Balance],[Price],[ChangeNumber],[ChangePrice],[ChangeType],[ChangeID],[PreBalance],[PrePrice] FROM [Balance] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" WHERE " + strWhere);
            }
            if (orderBy.Trim() != "")
            {
                strSql.Append(" ORDER BY " + orderBy);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取最新结余信息
        /// </summary>
        /// <param name="StrWhere"></param>
        /// <returns></returns>
        public DataTable GetLastList(string StrWhere)
        {
            StringBuilder strSql = new StringBuilder("");
            strSql.Append("SELECT * FROM (SELECT *,RANK() OVER(PARTITION BY ClassID,RawID,InDBID ORDER BY AddDate DESC,ID DESC ) AS [Rank] FROM [Balance]) AS Tab WHERE [Rank]=1 ");
            if (StrWhere.Trim() != "")
            {
                strSql.Append(" AND " + StrWhere);
            }
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获得数量
        /// </summary>
        public int GetCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(*) FROM [Balance] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" WHERE " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            int result = 0;
            Int32.TryParse(obj.ToString(), out result);
            return result;
        }

        public decimal GetBalance(int ClassID, string StrInDBID, DateTime AddDate)
        {
            string sql = "SELECT MIN(Balance) FROM [Balance] WHERE ClassID=@ClassID AND InDBID=@InDBID AND AddDate>= @AddDate ";
            SqlParameter[] parameters = {
                    new SqlParameter("@InDBId", SqlDbType.NVarChar,50),
					new SqlParameter("@ClassID", SqlDbType.Int,4),
					new SqlParameter("@AddDate", SqlDbType.DateTime,12)};
            parameters[0].Value = StrInDBID;
            parameters[1].Value = ClassID;
            parameters[2].Value = AddDate;
            object obj = DbHelperSQL.GetSingle(sql, parameters);
            decimal result = -1;
            if (obj != null) Decimal.TryParse(obj.ToString(), out result);
            return result;
        }
    }
}
